package work.vcloud.baseinfo.controller;

import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import work.vcloud.baseinfo.utils.ExportExcelUtil;
import work.vcloud.frame.controller.BaseController;
import work.vcloud.frame.dao.OpenDao;
import work.vcloud.frame.vo.OutputVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.RoundingMode;
import java.text.NumberFormat;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@RestController
@Scope("prototype")
public class ExportController extends BaseController {

	@Value("${web.upload_path}")
	private String filePath;
	
	@Autowired
	OpenDao openDao;
	
	/**
	 * 获取导出参数
	 */
	public void getParamList(Map<String, Object> map) throws Exception {
		map.put("ifexport", "1");
		map.put("sqlMapId", "port.select");
		List<Map<String, Object>> list = openDao.queryForList(map);
		map.put("headList", list);
		StringBuffer buffer = new StringBuffer();
		if(list.size()>0) {
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> data =list.get(i);
				if(data.get("fk_rule") != null) {
					buffer.append(",(select name from baseinfo.sc_dictionary where fk_rule = '" + data.get("fk_rule").toString()+"' and id = "+data.get("col").toString()+") "+data.get("col"));
				}else if(data.get("fk_form") != null) {
					if(data.get("fk_form").toString().equals("sc_prop")) {
						buffer.append(",(select prop_value from baseinfo.sc_prop where prop_pk = "+data.get("col").toString()+") "+data.get("col"));
					}else if(data.get("fk_form").toString().equals("sc_class_teacher")) {
						if(data.get("col").toString().equals("zbzr")) {
							buffer.append(",(select name from baseinfo.sc_user_teacher where id = (select fk_teacher from baseinfo.sc_class_teacher where status = '0' and type = '1' and fk_class = form.id)) zbzr");
						}else {
							buffer.append(",(select name from baseinfo.sc_user_teacher where id = (select fk_teacher from baseinfo.sc_class_teacher where status = '0' and type = '2' and fk_class = form.id)) fbzr");
						}
					}else {
						buffer.append(",(select name from baseinfo."+data.get("fk_form").toString()+" where id = form."+data.get("col").toString()+") "+data.get("col"));
					}
				}
				else {
					buffer.append(","+data.get("col"));
				}
			}
			//去掉第一个逗号
			if(!"".equals(buffer.toString())) {
				map.put("exportParams", buffer.substring(1));
			}
		}
	}

	/**
	 * 导出教师信息
	 */
	@RequestMapping("api/teacher.export")
	public OutputVo exportTeacher() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}

			//获取参数
			map.put("form", "sc_user_teacher");
			getParamList(map);
			map.put("sqlMapId", "teacher.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "教师导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出学生信息
	 */
	@RequestMapping("api/student.export")
	public OutputVo exportStudent(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_user_student");
			getParamList(map);
			
			map.put("sqlMapId", "student.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "学生导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}



	/**
	 * 导出学生信息
	 */
	@RequestMapping("api/student.exportXj")
	public OutputVo exportStudentXj(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");


			map.put("sqlMapId","student.selectForXj");
			List<Map<String, Object>> studentList = openDao.queryForList(map);


			String names = "学生信息表.xls";
			String fileName = filePath + "excelTemplate/"+names;
			studentxjExport(studentList,fileName);

			return output("0000", "", "excelTemplate/"+names);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}


	private void studentxjExport(List<Map<String, Object>> studentList , String fileName) throws IOException {
		/**
		 *  NumberFormat类进行数据处理
		 *  */
		NumberFormat numberInstance = NumberFormat.getNumberInstance();
//        设置最大小数点位数
		numberInstance.setMaximumFractionDigits(2);
//        设置数据舍入类型
		numberInstance.setRoundingMode(RoundingMode.HALF_UP);

		//创建工作簿 类似于创建Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 设置表头字体属性
		Font font = workbook.createFont();        // 创建字体对象
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置字体加粗
		font.setFontHeightInPoints((short)13);	// 设置字体大小
		//标题行的样式
		CellStyle cellStyleHead = workbook.createCellStyle();
		cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		cellStyleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		cellStyleHead.setFont(font);// 将字体应用到样式
		// 设置表头字体属性
		font = workbook.createFont();        // 创建字体对象
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置字体加粗
		font.setFontHeightInPoints((short)13);	// 设置字体大小
		//标题行的样式
		cellStyleHead = workbook.createCellStyle();
		cellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		cellStyleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		cellStyleHead.setFont(font);// 将字体应用到样式
		//设置内容样式
		HSSFCellStyle cstyle = workbook.createCellStyle();
		cstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		cstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		cstyle.setWrapText(true);//自动换行
		//设置内容样式
		HSSFCellStyle contentStyle = workbook.createCellStyle();
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//上下左右一个个设置边框，细线
		contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//单元格的边框颜色默认黑色
		contentStyle.setWrapText(true);//自动换行





		//创建 sheetname页名
		HSSFSheet sheet = workbook.createSheet("Sheet1");



		//但是设置单元格样式需要单元格创建了作为前提，否则报错
		for (int i = 1; i <= 15; i++) {
			// 设置列宽
			sheet.setColumnWidth(i, 20 * 170);
		}
		//创建标题行,下标从0开始
		HSSFRow row = sheet.createRow(0);
		//创建这行中的列,并给该列直接赋值，标题行就只有一列
		row.createCell(0).setCellValue("姓名");
		row.createCell(1).setCellValue("班级");
		row.createCell(2).setCellValue("身份证件号");
		row.createCell(3).setCellValue("电子注册号");
		row.createCell(4).setCellValue("学籍证号");
		row.setHeight((short) 500);
		for (Cell cell : row) {
			cell.setCellStyle(cellStyleHead);
		}
		int i = 0;

		for (Map<String, Object> objectMap : studentList) {
			i += 1;
			row = sheet.createRow(i);

			row.createCell(0).setCellValue((String) objectMap.get("student_name"));
			row.createCell(1).setCellValue((String) objectMap.get("class_name"));
			row.createCell(2).setCellValue((String) objectMap.get("sfzjh"));


			row.setHeight((short) 500);
			for (Cell cell : row) {
				cell.setCellStyle(contentStyle);
			}
		}


		//设定 路径
		FileOutputStream stream = new FileOutputStream(new File(fileName));
		// 写入工作表
		workbook.write(stream);
		//关流
		stream.close();
	}

	
	/**
	 * 导出主管单位信息
	 */
	@RequestMapping("api/unit.exportCenter")
	public OutputVo exportCenter(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_unit_center");
			getParamList(map);
			
			map.put("sqlMapId", "unit.selectExportCenter");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "主管单位导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出学校信息
	 */
	@RequestMapping("api/unit.exportSchool")
	public OutputVo exportSchool(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_unit_school");
			getParamList(map);
			
			map.put("sqlMapId", "unit.selectExportSchool");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "学校导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出附属学校信息
	 */
	@RequestMapping("api/unit.exportAttach")
	public OutputVo exportAttach(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_unit_school_attach");
			getParamList(map);
			
			map.put("sqlMapId", "unit.selectExportAttach");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "附属学校导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出年级信息
	 */
	@RequestMapping("api/grade.export")
	public OutputVo exportGrade(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_unit_grade_relate");
			getParamList(map);
			
			map.put("sqlMapId", "unit_grade.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "年级导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出班级信息
	 */
	@RequestMapping("api/class.export")
	public OutputVo exportClass(HttpServletRequest request, HttpServletResponse presponse) {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_class");
			getParamList(map);
			
			map.put("sqlMapId", "class.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "班级导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出课程信息
	 */
	@RequestMapping("api/course.export")
	public OutputVo exportCourse() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_course");
			getParamList(map);
			
			map.put("sqlMapId", "course.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "课程导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出上课时间信息
	 */
	@RequestMapping("api/course_time.export")
	public OutputVo exportCourseTime() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_course_time");
			getParamList(map);
			
			map.put("sqlMapId", "course_time.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "上课时间导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	/**
	 * 导出任课计划信息
	 */
	@RequestMapping("api/course_info.export")
	public OutputVo exportCourseInfo() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_course_info");
			getParamList(map);
			
			map.put("sqlMapId", "course_info.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "任课信息导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	
	/**
	 * 导出excel配置信息
	 */
	@RequestMapping("api/port.export")
	public OutputVo exportPort() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			JSONArray idlist = JSONArray.parseArray(map.get("list").toString());

			if(idlist.size()>0) {
				String ids = "";
				for (int i = 0; i < idlist.size(); i++) {
					ids = ids + ",'" + idlist.get(i) + "'";
				}
				map.put("id", ids.substring(1));
			}
			//获取参数
			map.put("form", "sc_excel_setting");
			getParamList(map);
			
			map.put("sqlMapId", "port.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "excel配置导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	
	
	/**
	 * 导出菜单信息
	 */
	@RequestMapping("api/menu.export")
	public OutputVo exportMenu() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			
			//获取参数
			map.put("form", "sc_menu");
			getParamList(map);
			
			map.put("sqlMapId", "menu.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "菜单导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
	/**
	 * 导出按钮信息
	 */
	@RequestMapping("api/button.export")
	public OutputVo exportButton() {
		try {
			Map<String, Object> map = getParameterMap();
			map.remove("page");
			
			//获取参数
			map.put("form", "sc_button");
			getParamList(map);
			
			map.put("sqlMapId", "button.selectExport");
	        List<Map<String,Object>> dataList =openDao.queryForList(map);
	        
	        List<Map<String,Object>> headList = (List)map.get("headList");
	       
	        String fileName = "按钮导出数据.xlsx";
	        ExportExcelUtil.writeExcel(headList, dataList, filePath+"excelExport/", fileName);
	        
	        return output("0000", "", "excelExport/"+fileName);
		} catch (Exception e) {
			e.printStackTrace();
			return output("9999", "", e.getMessage().toString());
		}
	}
}